{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9d679d4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_人事月报可视化数据处理.ipynb\n",
    "# Create By GF 2023-11-20 17:40"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1f7720a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2cde5140",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"人事数据\")\n",
    "df2 = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"毛利\")\n",
    "df3 = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"礼赠积分\")\n",
    "df4 = pd.read_excel(\"./人事月报基础数据_2023-09-30.xlsx\", sheet_name=\"运营扣减\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dd991ba3-2242-4fac-93cd-20fb24c9cb12",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 列重命名\n",
    "Rename1 = df1\n",
    "Rename2 = df2\n",
    "Rename3 = df3\n",
    "Rename4 = df4\n",
    "# --------------------------------------------------\n",
    "Rename2 = Rename2.rename(columns={\"业务职员代码\":\"职员代码\"})\n",
    "Rename3 = Rename3.rename(columns={\"业务职员代码\":\"职员代码\"})\n",
    "Rename4 = Rename4.rename(columns={\"业务职员代码\":\"职员代码\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "393dadf2-b524-4075-992a-70d9703182b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 筛选字段\n",
    "Filed_Name1 = [\"职员代码\", \"姓名\", \"门店类型\", \"部门\", \"一级部门\", \"职位\", \"职级\", \"员工类别\", \"入职日期\", \"社保挂靠单位\", \"评级23\", \"出生日期\", \"性别\", \"最高学历\"]\n",
    "Filed_Name2 = [\"职员代码\", \"考核毛利\"]\n",
    "Filed_Name3 = [\"职员代码\", \"POS收款金额\"]\n",
    "Filed_Name4 = [\"职员代码\", \"单价\"]\n",
    "# --------------------------------------------------\n",
    "Filter1 = Rename1[Filed_Name1]\n",
    "Filter2 = Rename2[Filed_Name2]\n",
    "Filter3 = Rename3[Filed_Name3]\n",
    "Filter4 = Rename4[Filed_Name4]\n",
    "# --------------------------------------------------\n",
    "Filter2 = Filter2.groupby(\"职员代码\").sum().reset_index() # -> 聚合\"毛利\"表格。\n",
    "Filter3 = Filter3.groupby(\"职员代码\").sum().reset_index() # -> 聚合\"礼赠积分\"表格。\n",
    "Filter4 = Filter4.groupby(\"职员代码\").sum().reset_index() # -> 聚合\"运营扣减\"表格。\n",
    "# --------------------------------------------------\n",
    "Filter1 = Filter1[Filter1[\"员工类别\"] != \"离职\"] # -> 因为要统计离职人员, 不能筛除。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "07a64755-d259-4c0c-9388-afc07f70f982",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>职员代码</th>\n",
       "      <th>姓名</th>\n",
       "      <th>门店类型</th>\n",
       "      <th>部门</th>\n",
       "      <th>一级部门</th>\n",
       "      <th>职位</th>\n",
       "      <th>职级</th>\n",
       "      <th>员工类别</th>\n",
       "      <th>入职日期</th>\n",
       "      <th>社保挂靠单位</th>\n",
       "      <th>评级23</th>\n",
       "      <th>出生日期</th>\n",
       "      <th>性别</th>\n",
       "      <th>最高学历</th>\n",
       "      <th>考核毛利</th>\n",
       "      <th>POS收款金额</th>\n",
       "      <th>单价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P1544-16</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>总裁秘书</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1992-08-09</td>\n",
       "      <td>女</td>\n",
       "      <td>本科</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P2292-23</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>秘书长</td>\n",
       "      <td>17</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2005-09-23</td>\n",
       "      <td>退休</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1963-12-01</td>\n",
       "      <td>女</td>\n",
       "      <td>大专</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P275-6</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>机要员</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2007-05-11</td>\n",
       "      <td>退休</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1971-08-12</td>\n",
       "      <td>女</td>\n",
       "      <td>大专</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P100-12</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>机要员</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2003-06-24</td>\n",
       "      <td>退休</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1965-12-17</td>\n",
       "      <td>女</td>\n",
       "      <td>中专</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P2110-14</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>行政秘书</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2021-10-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1996-10-05</td>\n",
       "      <td>女</td>\n",
       "      <td>本科</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1128</th>\n",
       "      <td>CQ1-2103008</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆荣昌人民路形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>门店店长</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-10-20</td>\n",
       "      <td>男</td>\n",
       "      <td>大专</td>\n",
       "      <td>72.0</td>\n",
       "      <td>503.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1129</th>\n",
       "      <td>CQ1-2302024</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆荣昌人民路形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>专业导购</td>\n",
       "      <td>10</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2023-02-11</td>\n",
       "      <td>待定</td>\n",
       "      <td>中</td>\n",
       "      <td>2003-04-16</td>\n",
       "      <td>男</td>\n",
       "      <td>中专</td>\n",
       "      <td>133974.0</td>\n",
       "      <td>13634.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1130</th>\n",
       "      <td>CQ1-1402006</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆铜梁步行街形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>门店店长</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1995-03-19</td>\n",
       "      <td>男</td>\n",
       "      <td>高中</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1131</th>\n",
       "      <td>CQ1-1903036</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆铜梁步行街形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>专业导购</td>\n",
       "      <td>10</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>中</td>\n",
       "      <td>2000-02-22</td>\n",
       "      <td>男</td>\n",
       "      <td>高中</td>\n",
       "      <td>131521.0</td>\n",
       "      <td>9445.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1132</th>\n",
       "      <td>CQ1-2302050</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆铜梁步行街形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>专业导购</td>\n",
       "      <td>10</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2023-02-25</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>中</td>\n",
       "      <td>1996-10-06</td>\n",
       "      <td>男</td>\n",
       "      <td>高中</td>\n",
       "      <td>151922.0</td>\n",
       "      <td>10895.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1133 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             职员代码  姓名 门店类型          部门   一级部门    职位  职级  员工类别       入职日期  \\\n",
       "0        P1544-16  李华  NaN         秘书处    总裁办  总裁秘书  13  正式员工 2015-09-01   \n",
       "1        P2292-23  李华  NaN         秘书处    总裁办   秘书长  17  正式员工 2005-09-23   \n",
       "2          P275-6  李华  NaN         秘书处    总裁办   机要员  13  正式员工 2007-05-11   \n",
       "3         P100-12  李华  NaN         秘书处    总裁办   机要员  13  正式员工 2003-06-24   \n",
       "4        P2110-14  李华  NaN         秘书处    总裁办  行政秘书  13  正式员工 2021-10-21   \n",
       "...           ...  ..  ...         ...    ...   ...  ..   ...        ...   \n",
       "1128  CQ1-2103008  李华  形象店  重庆荣昌人民路形象店  重庆分公司  门店店长  13  正式员工 2021-03-01   \n",
       "1129  CQ1-2302024  李华  形象店  重庆荣昌人民路形象店  重庆分公司  专业导购  10  正式员工 2023-02-11   \n",
       "1130  CQ1-1402006  李华  形象店  重庆铜梁步行街形象店  重庆分公司  门店店长  13  正式员工 2021-03-11   \n",
       "1131  CQ1-1903036  李华  形象店  重庆铜梁步行街形象店  重庆分公司  专业导购  10  正式员工 2019-03-20   \n",
       "1132  CQ1-2302050  李华  形象店  重庆铜梁步行街形象店  重庆分公司  专业导购  10  正式员工 2023-02-25   \n",
       "\n",
       "             社保挂靠单位 评级23       出生日期   性别 最高学历      考核毛利  POS收款金额  单价  \n",
       "0               NaN  NaN 1992-08-09    女   本科       NaN      NaN NaN  \n",
       "1                退休  NaN 1963-12-01    女   大专       NaN      NaN NaN  \n",
       "2                退休  NaN 1971-08-12  女     大专       NaN      NaN NaN  \n",
       "3                退休  NaN 1965-12-17    女   中专       NaN      NaN NaN  \n",
       "4               NaN  NaN 1996-10-05    女   本科       NaN      NaN NaN  \n",
       "...             ...  ...        ...  ...  ...       ...      ...  ..  \n",
       "1128  重庆市渝蓉讯捷通讯有限公司  NaN 1995-10-20    男   大专      72.0    503.0 NaN  \n",
       "1129             待定    中 2003-04-16    男   中专  133974.0  13634.0 NaN  \n",
       "1130  重庆市渝蓉讯捷通讯有限公司  NaN 1995-03-19    男   高中       NaN      NaN NaN  \n",
       "1131  重庆市渝蓉讯捷通讯有限公司    中 2000-02-22    男   高中  131521.0   9445.0 NaN  \n",
       "1132  重庆市渝蓉讯捷通讯有限公司    中 1996-10-06    男   高中  151922.0  10895.0 NaN  \n",
       "\n",
       "[1133 rows x 17 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 连接查询\n",
    "Joined = pd.merge(left=Filter1, right=Filter2, how=\"left\", on=\"职员代码\")\n",
    "Joined = pd.merge(left=Joined,  right=Filter3, how=\"left\", on=\"职员代码\")\n",
    "Joined = pd.merge(left=Joined,  right=Filter4, how=\"left\", on=\"职员代码\")\n",
    "Joined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "529cdce8-77b4-425f-a915-55d0359fe6f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 类型调整\n",
    "ChangeType = Joined.copy()\n",
    "# --------------------------------------------------\n",
    "ChangeType[\"职级\"] = ChangeType[\"职级\"].astype(\"int64\")\n",
    "ChangeType[\"入职日期\"] = ChangeType[\"入职日期\"].astype(\"datetime64[ns]\")\n",
    "ChangeType[\"出生日期\"] = ChangeType[\"出生日期\"].astype(\"datetime64[ns]\")\n",
    "ChangeType[\"门店类型\"] = ChangeType[\"门店类型\"].fillna(value=np.nan)\n",
    "ChangeType[\"最高学历\"] = ChangeType[\"最高学历\"].fillna(value=\"未登记\")\n",
    "ChangeType[\"社保挂靠单位\"] = ChangeType[\"社保挂靠单位\"].fillna(value=\"Null\") # -> 因为 numpy.nan 是 float 类型, 不可用于字符串判断。\n",
    "ChangeType[\"POS收款金额\"] = ChangeType[\"POS收款金额\"].fillna(value=0.0)\n",
    "ChangeType[\"单价\"] = ChangeType[\"单价\"].fillna(value=0.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "e900a6d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>职员代码</th>\n",
       "      <th>姓名</th>\n",
       "      <th>门店类型</th>\n",
       "      <th>部门</th>\n",
       "      <th>一级部门</th>\n",
       "      <th>职位</th>\n",
       "      <th>职级</th>\n",
       "      <th>员工类别</th>\n",
       "      <th>入职日期</th>\n",
       "      <th>社保挂靠单位</th>\n",
       "      <th>...</th>\n",
       "      <th>员工司龄</th>\n",
       "      <th>员工年龄</th>\n",
       "      <th>真实毛利</th>\n",
       "      <th>员工司龄结构</th>\n",
       "      <th>员工年龄结构</th>\n",
       "      <th>人资系统类型</th>\n",
       "      <th>场所(门店/后台)</th>\n",
       "      <th>真实毛利结构</th>\n",
       "      <th>员工状态性质</th>\n",
       "      <th>员工岗位性质</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P1544-16</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>总裁秘书</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>Null</td>\n",
       "      <td>...</td>\n",
       "      <td>98.71</td>\n",
       "      <td>31.29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G. 5-10年</td>\n",
       "      <td>31-35岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>后台人员</td>\n",
       "      <td>None</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P2292-23</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>秘书长</td>\n",
       "      <td>17</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2005-09-23</td>\n",
       "      <td>退休</td>\n",
       "      <td>...</td>\n",
       "      <td>217.98</td>\n",
       "      <td>59.98</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H. 10年及以上</td>\n",
       "      <td>56岁及以上</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>后台人员</td>\n",
       "      <td>None</td>\n",
       "      <td>C. 返聘</td>\n",
       "      <td>A. 高层管理</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P275-6</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>机要员</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2007-05-11</td>\n",
       "      <td>退休</td>\n",
       "      <td>...</td>\n",
       "      <td>198.43</td>\n",
       "      <td>52.28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H. 10年及以上</td>\n",
       "      <td>46-55岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>后台人员</td>\n",
       "      <td>None</td>\n",
       "      <td>C. 返聘</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P100-12</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>机要员</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2003-06-24</td>\n",
       "      <td>退休</td>\n",
       "      <td>...</td>\n",
       "      <td>244.98</td>\n",
       "      <td>57.93</td>\n",
       "      <td>NaN</td>\n",
       "      <td>H. 10年及以上</td>\n",
       "      <td>56岁及以上</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>后台人员</td>\n",
       "      <td>None</td>\n",
       "      <td>C. 返聘</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P2110-14</td>\n",
       "      <td>李华</td>\n",
       "      <td>NaN</td>\n",
       "      <td>秘书处</td>\n",
       "      <td>总裁办</td>\n",
       "      <td>行政秘书</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2021-10-21</td>\n",
       "      <td>Null</td>\n",
       "      <td>...</td>\n",
       "      <td>25.05</td>\n",
       "      <td>27.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E. 2年</td>\n",
       "      <td>26-30岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>后台人员</td>\n",
       "      <td>None</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1128</th>\n",
       "      <td>CQ1-2103008</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆荣昌人民路形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>门店店长</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2021-03-01</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>...</td>\n",
       "      <td>32.74</td>\n",
       "      <td>28.09</td>\n",
       "      <td>-431.0</td>\n",
       "      <td>E. 2年</td>\n",
       "      <td>26-30岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>门店人员</td>\n",
       "      <td>20万以下</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1129</th>\n",
       "      <td>CQ1-2302024</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆荣昌人民路形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>专业导购</td>\n",
       "      <td>10</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2023-02-11</td>\n",
       "      <td>待定</td>\n",
       "      <td>...</td>\n",
       "      <td>9.35</td>\n",
       "      <td>20.60</td>\n",
       "      <td>120340.0</td>\n",
       "      <td>C. 6-12个月</td>\n",
       "      <td>21-25岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>门店人员</td>\n",
       "      <td>20万以下</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1130</th>\n",
       "      <td>CQ1-1402006</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆铜梁步行街形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>门店店长</td>\n",
       "      <td>13</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2021-03-11</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>...</td>\n",
       "      <td>32.41</td>\n",
       "      <td>28.68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E. 2年</td>\n",
       "      <td>26-30岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>门店人员</td>\n",
       "      <td>None</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1131</th>\n",
       "      <td>CQ1-1903036</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆铜梁步行街形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>专业导购</td>\n",
       "      <td>10</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2019-03-20</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>...</td>\n",
       "      <td>56.13</td>\n",
       "      <td>23.75</td>\n",
       "      <td>122076.0</td>\n",
       "      <td>F. 3-5年</td>\n",
       "      <td>21-25岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>门店人员</td>\n",
       "      <td>20万以下</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1132</th>\n",
       "      <td>CQ1-2302050</td>\n",
       "      <td>李华</td>\n",
       "      <td>形象店</td>\n",
       "      <td>重庆铜梁步行街形象店</td>\n",
       "      <td>重庆分公司</td>\n",
       "      <td>专业导购</td>\n",
       "      <td>10</td>\n",
       "      <td>正式员工</td>\n",
       "      <td>2023-02-25</td>\n",
       "      <td>重庆市渝蓉讯捷通讯有限公司</td>\n",
       "      <td>...</td>\n",
       "      <td>8.89</td>\n",
       "      <td>27.13</td>\n",
       "      <td>141027.0</td>\n",
       "      <td>C. 6-12个月</td>\n",
       "      <td>26-30岁</td>\n",
       "      <td>后台职能</td>\n",
       "      <td>门店人员</td>\n",
       "      <td>20万以下</td>\n",
       "      <td>A. 全职</td>\n",
       "      <td>E. 专员/销售/收银</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1133 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             职员代码  姓名 门店类型          部门   一级部门    职位  职级  员工类别       入职日期  \\\n",
       "0        P1544-16  李华  NaN         秘书处    总裁办  总裁秘书  13  正式员工 2015-09-01   \n",
       "1        P2292-23  李华  NaN         秘书处    总裁办   秘书长  17  正式员工 2005-09-23   \n",
       "2          P275-6  李华  NaN         秘书处    总裁办   机要员  13  正式员工 2007-05-11   \n",
       "3         P100-12  李华  NaN         秘书处    总裁办   机要员  13  正式员工 2003-06-24   \n",
       "4        P2110-14  李华  NaN         秘书处    总裁办  行政秘书  13  正式员工 2021-10-21   \n",
       "...           ...  ..  ...         ...    ...   ...  ..   ...        ...   \n",
       "1128  CQ1-2103008  李华  形象店  重庆荣昌人民路形象店  重庆分公司  门店店长  13  正式员工 2021-03-01   \n",
       "1129  CQ1-2302024  李华  形象店  重庆荣昌人民路形象店  重庆分公司  专业导购  10  正式员工 2023-02-11   \n",
       "1130  CQ1-1402006  李华  形象店  重庆铜梁步行街形象店  重庆分公司  门店店长  13  正式员工 2021-03-11   \n",
       "1131  CQ1-1903036  李华  形象店  重庆铜梁步行街形象店  重庆分公司  专业导购  10  正式员工 2019-03-20   \n",
       "1132  CQ1-2302050  李华  形象店  重庆铜梁步行街形象店  重庆分公司  专业导购  10  正式员工 2023-02-25   \n",
       "\n",
       "             社保挂靠单位  ...    员工司龄   员工年龄      真实毛利     员工司龄结构  员工年龄结构  人资系统类型  \\\n",
       "0              Null  ...   98.71  31.29       NaN   G. 5-10年  31-35岁    后台职能   \n",
       "1                退休  ...  217.98  59.98       NaN  H. 10年及以上  56岁及以上    后台职能   \n",
       "2                退休  ...  198.43  52.28       NaN  H. 10年及以上  46-55岁    后台职能   \n",
       "3                退休  ...  244.98  57.93       NaN  H. 10年及以上  56岁及以上    后台职能   \n",
       "4              Null  ...   25.05  27.13       NaN      E. 2年  26-30岁    后台职能   \n",
       "...             ...  ...     ...    ...       ...        ...     ...     ...   \n",
       "1128  重庆市渝蓉讯捷通讯有限公司  ...   32.74  28.09    -431.0      E. 2年  26-30岁    后台职能   \n",
       "1129             待定  ...    9.35  20.60  120340.0  C. 6-12个月  21-25岁    后台职能   \n",
       "1130  重庆市渝蓉讯捷通讯有限公司  ...   32.41  28.68       NaN      E. 2年  26-30岁    后台职能   \n",
       "1131  重庆市渝蓉讯捷通讯有限公司  ...   56.13  23.75  122076.0    F. 3-5年  21-25岁    后台职能   \n",
       "1132  重庆市渝蓉讯捷通讯有限公司  ...    8.89  27.13  141027.0  C. 6-12个月  26-30岁    后台职能   \n",
       "\n",
       "      场所(门店/后台)  真实毛利结构  员工状态性质       员工岗位性质  \n",
       "0          后台人员    None   A. 全职  E. 专员/销售/收银  \n",
       "1          后台人员    None   C. 返聘      A. 高层管理  \n",
       "2          后台人员    None   C. 返聘  E. 专员/销售/收银  \n",
       "3          后台人员    None   C. 返聘  E. 专员/销售/收银  \n",
       "4          后台人员    None   A. 全职  E. 专员/销售/收银  \n",
       "...         ...     ...     ...          ...  \n",
       "1128       门店人员   20万以下   A. 全职  E. 专员/销售/收银  \n",
       "1129       门店人员   20万以下   A. 全职  E. 专员/销售/收银  \n",
       "1130       门店人员    None   A. 全职  E. 专员/销售/收银  \n",
       "1131       门店人员   20万以下   A. 全职  E. 专员/销售/收银  \n",
       "1132       门店人员   20万以下   A. 全职  E. 专员/销售/收银  \n",
       "\n",
       "[1133 rows x 27 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算字段\n",
    "Compute = ChangeType.copy()\n",
    "# --------------------------------------------------\n",
    "Compute[\"性别\"] = Compute[\"性别\"].apply(lambda x: x.replace(' ', str('')))\n",
    "# --------------------------------------------------\n",
    "Compute[\"员工司龄\"] = round((datetime.now() - Compute[\"入职日期\"]) / np.timedelta64(1, 'M'), 2)\n",
    "Compute[\"员工年龄\"] = round((datetime.now() - Compute[\"出生日期\"]) / np.timedelta64(1, 'Y'), 2)\n",
    "Compute[\"最高学历\"] = Compute[\"最高学历\"].apply(lambda x: x.replace(\"小学\", \"初中\"))\n",
    "Compute[\"最高学历\"] = Compute[\"最高学历\"].apply(lambda x: x.replace(\"职高\", \"高中\"))\n",
    "Compute[\"真实毛利\"] = round(Compute[\"考核毛利\"] - Compute[\"POS收款金额\"] - Compute[\"单价\"], 2) # -> 系统的\"金额\"计算结果有误, 所以减\"单价\"。\n",
    "# --------------------------------------------------\n",
    "Compute[\"员工司龄结构\"] = None\n",
    "for Idx in Compute.index:\n",
    "    Month = Compute.loc[Idx, \"员工司龄\"]\n",
    "    # ----------------------------------------------\n",
    "    if Month < 3: Compute.loc[Idx, \"员工司龄结构\"] = \"A. 3个月以下\"\n",
    "    if  3 <= Month and Month <   6: Compute.loc[Idx, \"员工司龄结构\"] = \"B. 3-6个月\"\n",
    "    if  6 <= Month and Month <  12: Compute.loc[Idx, \"员工司龄结构\"] = \"C. 6-12个月\"\n",
    "    if 12 <= Month and Month <  24: Compute.loc[Idx, \"员工司龄结构\"] = \"D. 1年\"\n",
    "    if 24 <= Month and Month <  36: Compute.loc[Idx, \"员工司龄结构\"] = \"E. 2年\"\n",
    "    if 36 <= Month and Month <  60: Compute.loc[Idx, \"员工司龄结构\"] = \"F. 3-5年\"\n",
    "    if 60 <= Month and Month < 120: Compute.loc[Idx, \"员工司龄结构\"] = \"G. 5-10年\"\n",
    "    if Month >= 120: Compute.loc[Idx, \"员工司龄结构\"] = \"H. 10年及以上\"\n",
    "# --------------------------------------------------\n",
    "Compute[\"员工年龄结构\"] = None\n",
    "for Idx in Compute.index:\n",
    "    Age = Compute.loc[Idx, \"员工年龄\"]\n",
    "    # ----------------------------------------------\n",
    "    if Age <= 20: Compute.loc[Idx, \"员工年龄结构\"] = \"20岁及以下\"\n",
    "    if 20 < Age and Age <= 25: Compute.loc[Idx, \"员工年龄结构\"] = \"21-25岁\"\n",
    "    if 25 < Age and Age <= 30: Compute.loc[Idx, \"员工年龄结构\"] = \"26-30岁\"\n",
    "    if 30 < Age and Age <= 35: Compute.loc[Idx, \"员工年龄结构\"] = \"31-35岁\"\n",
    "    if 35 < Age and Age <= 45: Compute.loc[Idx, \"员工年龄结构\"] = \"36-45岁\"\n",
    "    if 45 < Age and Age <= 55: Compute.loc[Idx, \"员工年龄结构\"] = \"46-55岁\"\n",
    "    if Age > 55: Compute.loc[Idx, \"员工年龄结构\"] = \"56岁及以上\"\n",
    "# --------------------------------------------------\n",
    "Compute[\"人资系统类型\"] = None\n",
    "for Idx in Compute.index:\n",
    "    First_Level_Department = Compute.loc[Idx, \"一级部门\"]\n",
    "    Position = Compute.loc[Idx, \"职位\"]\n",
    "    # ----------------------------------------------\n",
    "    if   (\"零售\"   in First_Level_Department) and (\"中心\"     in First_Level_Department):\n",
    "        Compute.loc[Idx, \"人资系统类型\"] = \"销售系统\"\n",
    "    elif (\"分公司\" in First_Level_Department) and (\"总经理\"   in Position):\n",
    "        Compute.loc[Idx, \"人资系统类型\"] = \"销售系统\"\n",
    "    elif (\"分公司\" in First_Level_Department) and (\"大区经理\" in Position):\n",
    "        Compute.loc[Idx, \"人资系统类型\"] = \"销售系统\"\n",
    "    elif (\"分公司\" in First_Level_Department) and (\"数据\"     in Position):\n",
    "        Compute.loc[Idx, \"人资系统类型\"] = \"销售系统\"\n",
    "    else:\n",
    "        Compute.loc[Idx, \"人资系统类型\"] = \"后台职能\"\n",
    "# --------------------------------------------------\n",
    "Compute[\"场所(门店/后台)\"] = None\n",
    "for Idx in Compute.index:\n",
    "    Store = Compute.loc[Idx, \"门店类型\"]\n",
    "    # ----------------------------------------------\n",
    "    if Store ==    \"1+5\": Compute.loc[Idx, \"场所(门店/后台)\"] = \"门店人员\"\n",
    "    if Store == \"华为店\": Compute.loc[Idx, \"场所(门店/后台)\"] = \"门店人员\"\n",
    "    if Store == \"形象店\": Compute.loc[Idx, \"场所(门店/后台)\"] = \"门店人员\"\n",
    "    if Store == \"综合店\": Compute.loc[Idx, \"场所(门店/后台)\"] = \"门店人员\"\n",
    "    if Store is   np.nan: Compute.loc[Idx, \"场所(门店/后台)\"] = \"后台人员\"\n",
    "# --------------------------------------------------\n",
    "Compute[\"真实毛利结构\"] = None\n",
    "for Idx in Compute.index:\n",
    "    Profit = Compute.loc[Idx, \"真实毛利\"]\n",
    "    # ----------------------------------------------\n",
    "    if Profit < 200000: Compute.loc[Idx, \"真实毛利结构\"] = \"20万以下\"\n",
    "    if 200000 <= Profit and Profit <=  300000: Compute.loc[Idx, \"真实毛利结构\"] = \"20-30万\"\n",
    "    if 300000  < Profit and Profit <=  400000: Compute.loc[Idx, \"真实毛利结构\"] = \"30-40万\"\n",
    "    if 400000  < Profit and Profit <=  500000: Compute.loc[Idx, \"真实毛利结构\"] = \"40-50万\"\n",
    "    if 500000  < Profit and Profit <=  600000: Compute.loc[Idx, \"真实毛利结构\"] = \"50-60万\"\n",
    "    if 600000  < Profit and Profit <=  700000: Compute.loc[Idx, \"真实毛利结构\"] = \"60-70万\"\n",
    "    if 700000  < Profit and Profit <=  800000: Compute.loc[Idx, \"真实毛利结构\"] = \"70-80万\"\n",
    "    if 800000  < Profit and Profit <=  900000: Compute.loc[Idx, \"真实毛利结构\"] = \"80-90万\"\n",
    "    if 900000  < Profit and Profit <= 1000000: Compute.loc[Idx, \"真实毛利结构\"] = \"90-100万\"\n",
    "    if 1000000 < Profit and Profit <= 1100000: Compute.loc[Idx, \"真实毛利结构\"] = \"100-110万\"\n",
    "    if 1100000 < Profit and Profit <= 1200000: Compute.loc[Idx, \"真实毛利结构\"] = \"110-120万\"\n",
    "    if Profit > 1200000: Compute.loc[Idx, \"真实毛利结构\"] = \"120万以上\"\n",
    "# --------------------------------------------------\n",
    "Compute[\"员工状态性质\"] = None # 需要涉及 [全职, 试用, 返聘, 兼职, 残疾, 退休]\n",
    "for Idx in Compute.index:\n",
    "    Position = Compute.loc[Idx, \"职位\"]\n",
    "    Department = Compute.loc[Idx, \"部门\"]\n",
    "    Employee_Category = Compute.loc[Idx, \"员工类别\"]\n",
    "    Age = Compute.loc[Idx, \"员工年龄\"]\n",
    "    Social_Insurance = Compute.loc[Idx, \"社保挂靠单位\"]\n",
    "    # ----------------------------------------------\n",
    "    if   \"试用\"      in Employee_Category:\n",
    "        Compute.loc[Idx, \"员工状态性质\"] = \"B. 试用\"\n",
    "    elif (Age > 50) and (\"退休\" in Social_Insurance) and (\"退休组\" not in Department):\n",
    "        Compute.loc[Idx, \"员工状态性质\"] = \"C. 返聘\"\n",
    "    elif \"第三方\"    in Position:\n",
    "        Compute.loc[Idx, \"员工状态性质\"] = \"D. 兼职\"\n",
    "    elif \"残疾\"      in Position:\n",
    "        Compute.loc[Idx, \"员工状态性质\"] = \"E. 残疾\"\n",
    "    elif \"退休组\"    in Department:\n",
    "        Compute.loc[Idx, \"员工状态性质\"] = \"F. 退休\"\n",
    "    else:\n",
    "        Compute.loc[Idx, \"员工状态性质\"] = \"A. 全职\"\n",
    "# --------------------------------------------------\n",
    "Compute[\"员工岗位性质\"] = None\n",
    "for Idx in Compute.index:\n",
    "    Employee_Category = Compute.loc[Idx, \"员工类别\"]\n",
    "    Name = Compute.loc[Idx, \"姓名\"]\n",
    "    Position = Compute.loc[Idx, \"职位\"]\n",
    "    Rank = Compute.loc[Idx, \"职级\"]\n",
    "    # ----------------------------------------------\n",
    "    if (Rank == 19):                                                      Compute.loc[Idx, \"员工岗位性质\"] = \"A. 高层管理\"\n",
    "    if (Rank == 17) and (\"秘书长\" in Position or \"总\" in Position):       Compute.loc[Idx, \"员工岗位性质\"] = \"A. 高层管理\"\n",
    "    if (\"黄睿\" in Name):                                                  Compute.loc[Idx, \"员工岗位性质\"] = \"A. 高层管理\"\n",
    "    if (Rank == 17) and (\"部长\" in Position):                             Compute.loc[Idx, \"员工岗位性质\"] = \"B. 中层管理\"\n",
    "    if (Rank == 15) and (\"副部长\" in Position or \"大区经理\" in Position): Compute.loc[Idx, \"员工岗位性质\"] = \"C. 基层管理\"\n",
    "    if (\"主管\" in Position) or (\"店长\" in Position):                      Compute.loc[Idx, \"员工岗位性质\"] = \"D. 主管/店长\"\n",
    "    if (10 <= Rank) and (Rank <= 13):                                     Compute.loc[Idx, \"员工岗位性质\"] = \"E. 专员/销售/收银\"\n",
    "    if (Employee_Category == \"试用员工\"):                                 Compute.loc[Idx, \"员工岗位性质\"] = \"F. 实习/新生\"\n",
    "# --------------------------------------------------\n",
    "\n",
    "Compute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "97bcbb86",
   "metadata": {},
   "outputs": [],
   "source": [
    "Compute.to_csv(\"./XunJie_Human_Resources_Data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3c3c786-e987-40a7-98b7-da6bb7a93c8b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
